package org.huang.sample08.demo1;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @描述：
 * @作者: Mr.huang
 * @创建时间: 2017/8/21 12:00
 * @版本: 1.0
 */
@Service
public class StudentService {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public Student addStudent(final Student student){
        final String sql = "insert into t_student(name,age,birth) values (?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con)throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setObject(1,student.getName());
                ps.setObject(2,student.getAge());
                ps.setObject(3,student.getBirth());
                return ps;
            }
        }, keyHolder);

        student.setId(keyHolder.getKey().intValue());

        return student;
    }


    public List<Student> listStudent(){
        return  jdbcTemplate.query("select * from t_student", new StudentMapper());
    }


    public Student getStudent(String name){
        return jdbcTemplate.queryForObject("select * from t_student where name=?",new Object[]{name},new StudentMapper());
    }


    public void updateStudent(Student student){
        jdbcTemplate.update("update t_student set name=?,age=?,birth=?",new Object[]{student.getName(),student.getAge(),student.getBirth()});
    }



    private class StudentMapper implements RowMapper<Student> {

        @Override
        public Student mapRow(ResultSet rs, int i) throws SQLException {
            Student stu = new Student();
            stu.setId(rs.getInt("id"));
            stu.setAge(rs.getInt("age"));
            stu.setName(rs.getString("name"));
            stu.setBirth(rs.getDate("birth"));
            return stu;
        }
    }

}
